//
// Copyright (c) 2014, 保留所有权利。
//
// Restore.java
//
// 作    者：王红燕
// 当前版本：1.0
// 2014 下午1:44:22
//
// 
//
package com.vanke.agent.tools.merge;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import org.apache.log4j.Logger;
import org.wang.hongyan.common.lock.LockContext;

import com.vanke.agent.ADataSource;
import com.vanke.agent.C;
import com.vanke.agent.common.Cache;
import com.vanke.agent.common.CstUtil;
import com.vanke.agent.common.Util;
import com.vanke.agent.common.log.LoggerUtil;
import com.vanke.agent.tools.merge.bean.BackupCstInfo;
import com.vanke.agent.tools.merge.bean.BackupMapping;

/**
 *
 */
public class Restore {
	public static void main(String[] args) {
		Restore restore = new Restore();
		restore.restoreCst(args[0]);
		// restore.restoreCst("789D5818-969D-11E4-9720-00151788CEA4");
	}

	static Logger log = LoggerUtil.getLoggerByName("restore_merge");
	private Set<String> allTables = new HashSet<String>();

	/** 恢复合并备份 */
	public boolean restoreCst(String batchNo) {
		Connection bizCon = ADataSource.getConnection(ADataSource.SCHEMA_BIZ);

		BackupCstInfo retain = null;
		BackupCstInfo droped = null;
		boolean fromLock = false;
		boolean toLock = false;
		long from = 0, to = 0;
		try {
			List<BackupCstInfo> backupInfo = getBackupCstInfo(bizCon, batchNo);
			for (BackupCstInfo bci : backupInfo) {
				if (bci.getRetain() == 1) {
					retain = bci;
				} else {
					droped = bci;
				}
			}
			if (retain == null || droped == null) {
				log.warn("合并批次[" + batchNo + "]对应主档未找到");
				return false;
			}
			if (retain.getHandleStatus() != 0 || droped.getHandleStatus() != 0) {
				log.warn("非法恢复操作，合并批次[" + batchNo + "]已处理");
				return false;
			}
			from = droped.getCstGUID();
			to = retain.getCstGUID();
			log.info("[Restore]锁定客户[" + from + "," + to + "]");
			fromLock = LockContext.tryLock(from, C.LOCK_TRY_TIMEOUT);
			toLock = LockContext.tryLock(to, C.LOCK_TRY_TIMEOUT);
			if (!fromLock || !toLock) {
				if (fromLock) {
					LockContext.unlock(from);
				}
				if (toLock) {
					LockContext.unlock(to);
				}
				log.error("获取客户锁失败，无法执行恢复");
				return false;
			} // end if (!fromLock || !toLock)
			log.info("[Restore]成功锁定客户[" + from + "," + to + "]");
			return restoreCst(bizCon, retain, droped);
		} catch (Exception e) {
			e.printStackTrace();
			log.error("恢复异常：" + e);
			return false;
		} finally {
			if (fromLock) {
				LockContext.unlock(from);
			}
			if (toLock) {
				LockContext.unlock(to);
			}
			ADataSource.close(bizCon);
		}
	} //~restoreCst

	/** 恢复合并备份 */
	public boolean restoreCst(Connection bizCon, BackupCstInfo retain,
			BackupCstInfo droped) {
		allTables.addAll(Cache.loadTables(bizCon, "")); //

		Statement stmt = null;
		try {
			bizCon.setAutoCommit(false);
			stmt = bizCon.createStatement();

			//	1. 恢复客户主档
			log.info("恢复合并保留的主档");
			restoreRetainInfo(stmt, retain);
			log.info("恢复合并时丢弃的主档");
			restoreDroppedInfo(stmt, droped);
			//	2. 恢复映射关系
			log.info("恢复映射关系");
			restoreMapping(stmt, retain.getMappings());
			restoreMapping(stmt, droped.getMappings());
			//	3. 恢复推荐关系
			log.info("恢复推荐关系");
			restoreRecommend(stmt, retain, droped);
			//	4. 恢复跟进记录
			log.info("恢复跟进记录");
			restoreFollow(stmt, retain, droped);
			//	5. 恢复订购关系
			log.info("恢复订购关系");
			restoreProductOrder(stmt, retain, droped);
			//	6. 恢复会员信息
			log.info("恢复会员信息");
			restoreMember(stmt, retain, droped);
			//	7. 恢复联名ID
			log.info("恢复联名ID");
			restoreCstAccount(stmt, retain, droped);

			log.info("执行恢复");
			stmt.executeBatch();
			log.info("提交恢复");
			bizCon.commit();
			log.info("恢复成功！");

			updateStatus(retain.getBatchNo()); // 更新处理状态
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			log.error("恢复异常：" + e);
			try {
				bizCon.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			return false;
		} finally {
			ADataSource.close(stmt);
		}
	} //~restoreCst

	private void updateStatus(String batchNo) {
		Connection con = ADataSource.getConnection(ADataSource.SCHEMA_BIZ);
		PreparedStatement pstmt = null;
		String sql = "update backup_cst set HandleStatus=1 where batch_no=?";
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, batchNo);
			pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			ADataSource.close(null, pstmt, con);
		}
	} //~

	/** 获取备份主档 */
	protected List<BackupCstInfo> getBackupCstInfo(Connection con,
			String batchNo) {
		List<BackupCstInfo> backupInfo = new ArrayList<BackupCstInfo>(2);
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			/* 加载主档 */
			String sql = "select * from backup_cst where batch_no=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, batchNo);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				BackupCstInfo bci = new BackupCstInfo();
				bci.setCardIdList(rs.getString("CardIdList"));
				bci.setOriGUIDList(rs.getString("OriGUIDList"));
				bci.setContact(rs.getString("contact"));
				bci.setCstGUID(rs.getLong("CstGUID"));
				bci.setRetain(rs.getInt("retain"));
				bci.setMergeTime(rs.getTimestamp("merge_time"));
				bci.setHandleStatus(rs.getInt("HandleStatus"));
				bci.setBatchNo(batchNo);

				backupInfo.add(bci);
			} // end while
			if (backupInfo.size() == 0) {
				return backupInfo;
			}
			ADataSource.close(rs, pstmt, null);

			// 加载映射信息
			sql = "select * from backup_mapping where batch_no=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, batchNo);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				BackupMapping mb = new BackupMapping();
				mb.setIdentity(rs.getString("identity"));	// 手机、证件号或明源GUID
				mb.setType(rs.getInt("type"));				// 1:手机，2:身份证，3:明源GUID
				mb.setCstGUID(rs.getLong("CstGUID"));		// 数据中心客户GUID，对应保留或丢弃客户的ID
				mb.setBatchNo(batchNo); 					// 对应backup_cst:batch_no
				for (BackupCstInfo bci : backupInfo) {
					if (bci.getCstGUID() == mb.getCstGUID()) {
						bci.getMappings().add(mb);
					}
				} // end for
			} // end while
		} catch (Exception e) {
			e.printStackTrace();
			log.error("获取备份信息异常");
			return null;
		} finally {
			ADataSource.close(rs, pstmt, null);
		}
		return backupInfo;
	} //~getBackupBackCstInfo

	/** 恢复客户主档信息 */
	protected void restoreRetainInfo(Statement stmt, BackupCstInfo bci)
			throws SQLException {
		String table = CstUtil.retrieveCustomerTable(bci.getCstGUID());
		String sql = "update " + table + " c,backup_cst b set ";
		String[] fs = C.DC_FIELDS_CST.split(",");
		for (int i = 0; i < fs.length; i++) {
			sql += (i > 0 ? "," : "") + "c." + fs[i] + "=" + "b." + fs[i];
		}
		sql += " where b.CstGUID=c.CstGUID ";
		sql += " and b.batch_no='" + bci.getBatchNo() + "'";
		sql += " and c.CstGUID=" + bci.getCstGUID();
		stmt.addBatch(sql);
		log.info(sql);
	} //~restoreRetainInfo

	/** 恢复客户主档信息 */
	protected void restoreDroppedInfo(Statement stmt, BackupCstInfo bci)
			throws SQLException {
		String table = CstUtil.retrieveCustomerTable(bci.getCstGUID());
		String fmt = "insert into %s(%s)";
		String insert = String.format(fmt, table, C.DC_FIELDS_CST);
		fmt = "(select %s from backup_cst where CstGUID=%s and batch_no='%s')";
		String select = String.format(fmt, C.DC_FIELDS_CST, bci.getCstGUID(),
				bci.getBatchNo());
		String sql = insert + select;
		stmt.addBatch(sql);
		log.info(sql);
	} //~restoreRetainInfo

	/** 恢复客户手机映射、身份证映射、明源GUID映射 */
	protected void restoreMapping(Statement stmt, List<BackupMapping> mappings)
			throws SQLException {
		String tbl;
		for (BackupMapping mb : mappings) {
			String identity = mb.getIdentity();
			String keyField;
			switch (mb.getType()) {
			case M.MAPPING_TYPE_MOBILE:
				tbl = CstUtil.retrieveMobileMapping(identity);
				keyField = "mobile";
				break;
			case M.MAPPING_TYPE_CARD:
				tbl = CstUtil.retrieveCardMapping(identity);
				keyField = "cardId";
				break;
			case M.MAPPING_TYPE_GUID:
				tbl = CstUtil.retrieveGUIDMapping(identity);
				keyField = "OriGUID";
				break;
			default:
				continue;
			} // end

			String sql = "update " + tbl + " set CstGUID=" + mb.getCstGUID();
			sql += " where " + keyField + "='" + identity + "'";
			stmt.addBatch(sql);
			log.info(sql);
		} // end for
	} //~restoreOriGUIDMapping

	/** 恢复客户推荐关系 */
	protected void restoreRecommend(Statement stmt, BackupCstInfo retain,
			BackupCstInfo droped) throws SQLException {
		long retainGuid = retain.getCstGUID();
		long dropedGuid = droped.getCstGUID();
		String retainTable = CstUtil.calcCstSplitTable(
				"prm_Agent_Cst_Recommed_", retainGuid);
		String dropedTable = CstUtil.calcCstSplitTable(
				"prm_Agent_Cst_Recommed_", dropedGuid);

		if (!allTables.contains(dropedTable)) {
			log.warn("table not found " + dropedTable);
			return;
		}

		String batchNo = retain.getBatchNo();

		if (retainTable.equals(dropedTable)) { // 在同一表中
			String sql = "update " + dropedTable
					+ " c,backup_recommend b set c.CstGUID=" + dropedGuid;
			sql += " where c.GUID=b.GUID";
			sql += " and b.CstGUID=" + dropedGuid;
			sql += " and c.CstMobile=b.CstMobile";
			sql += " and b.batch_no='" + droped.getBatchNo() + "'";
			stmt.addBatch(sql);
			log.info(sql);
		} else {
			long time = droped.getMergeTime().getTime() / 1000;

			String fmt = " CstGUID=%s and RecommTime<=%s and GUID not in(select GUID from backup_recommend where batch_no='%s' and CstGUID=%s)";
			String where = String.format(fmt, retainGuid, time, batchNo,
					retainGuid);

			fmt = "insert into %s(%s)(select %s from %s where %s)";
			String sql = String.format(fmt, dropedTable, Merge.RE_FIELDS,
					Merge.RE_FIELDS, retainTable, where);
			stmt.addBatch(sql);
			log.info(sql);

			fmt = "delete from %s where %s";
			sql = String.format(fmt, retainTable, where);
			stmt.addBatch(sql);
			log.info(sql);

			fmt = "update %s set CstGUID=%s where CstGUID=%s";
			sql = String.format(fmt, dropedTable, dropedGuid, retainGuid);
			stmt.addBatch(sql);
			log.info(sql);
		}
	} //~restoreRecommend

	/** 恢复客户跟进记录 */
	protected void restoreFollow(Statement stmt, BackupCstInfo retain,
			BackupCstInfo droped) throws SQLException {
		long dropedGuid = droped.getCstGUID();
		long retainGuid = retain.getCstGUID();
		String retainTable = CstUtil.retrieveCstReceiveTable(retainGuid);
		String dropedTable = CstUtil.retrieveCstReceiveTable(dropedGuid);

		if (!allTables.contains(dropedTable)) {
			log.warn("table not found " + dropedTable);
			return;
		}
		String idList = Util.convertToInSQL(droped.getOriGUIDList());
		if (idList == null) {
			log.warn("can't hit follow record ");
			return;
		}
		if (retainTable.equals(dropedTable)) { // 在同一表中
			String fmt = "update %s set CstGUID=%s where OriCstGUID in(%s)";
			String sql = String.format(fmt, dropedTable, dropedGuid, idList);
			stmt.addBatch(sql);
			log.info(sql);
		} else { // 在不同表中
			String fmt = "insert into %s(" + Merge.FR_FIELDS + ") (select "
					+ Merge.FR_FIELDS + " from %s where OriCstGUID in(%s))";
			// from -> to
			String sql = String.format(fmt, dropedTable, retainTable, idList);
			stmt.addBatch(sql);
			log.info(sql);
			fmt = "delete from %s where OriCstGUID in(%s)";

			// delete from
			sql = String.format(fmt, retainTable, idList);
			stmt.addBatch(sql);
			log.info(sql);

			fmt = "update %s set CstGUID=%s where OriCstGUID in(%s)";
			// update to
			sql = String.format(fmt, dropedTable, dropedGuid, idList);
			stmt.addBatch(sql);
			log.info(sql);
		}
	} //~restoreFollow

	/** 恢复客户订购关系 */
	protected void restoreProductOrder(Statement stmt, BackupCstInfo retain,
			BackupCstInfo droped) throws SQLException {
		long dropedGuid = droped.getCstGUID();
		long retainGuid = retain.getCstGUID();
		String retainTable = CstUtil.retrieveCustomerOrderTable(retainGuid);
		String dropedTable = CstUtil.retrieveCustomerOrderTable(dropedGuid);

		if (!allTables.contains(dropedTable)) {
			log.warn("table not found " + dropedTable);
			return;
		}
		String idList = Util.convertToInSQL(droped.getOriGUIDList());
		if (idList == null) {
			log.warn("can't hit product order ");
			return;
		}
		if (retainTable.equals(dropedTable)) { //  在同一表中
			String fmt = "update %s set CstGUID=%s where OriCstGUID in(%s)";
			String sql = String.format(fmt, dropedTable, dropedGuid, idList);
			stmt.addBatch(sql);
			log.info(sql);
		} else { // 在不同表中 
			String fmt = "insert into %s(" + Merge.PO_FIELDS + ") (select "
					+ Merge.PO_FIELDS + " from %s where OriCstGUID in(%s))";
			// from -> to
			String sql = String.format(fmt, dropedTable, retainTable, idList);
			stmt.addBatch(sql);
			log.info(sql);
			fmt = "delete from %s where OriCstGUID in(%s)";

			// delete from
			sql = String.format(fmt, retainTable, idList);
			stmt.addBatch(sql);
			log.info(sql);

			fmt = "update %s set CstGUID=%s where OriCstGUID in(%s)";
			// update to
			sql = String.format(fmt, dropedTable, dropedGuid, idList);
			stmt.addBatch(sql);
			log.info(sql);
		}
	} //~restoreProductOrder

	/** 恢复会员信息 */
	protected void restoreMember(Statement stmt, BackupCstInfo retain,
			BackupCstInfo droped) throws SQLException {
		long dropedGuid = droped.getCstGUID();
		String retainTable = CstUtil.retrieveMemberTable(retain.getCstGUID());
		String dropedTable = CstUtil.retrieveMemberTable(dropedGuid);

		if (!allTables.contains(dropedTable)) {
			log.warn("table not found " + dropedTable);
			return;
		}
		String idList = Util.convertToInSQL(droped.getOriGUIDList());
		if (idList == null) {
			log.warn("can't hit member ");
			return;
		}
		if (retainTable.equals(dropedTable)) { //  在同一表中
			String fmt = "update %s set CstGUID=%s where OriCstGUID in(%s)";
			// update to
			String sql = String.format(fmt, dropedTable, dropedGuid, idList);
			stmt.addBatch(sql);
			log.info(sql);
		} else { // 在不同表中 
			if (!allTables.contains(retainTable)) {
				log.warn("table not found " + retainTable);
				return;
			}
			// retainTable -> dropedTable
			String fmt = "insert into %s(" + Merge.MEM_FIELDS + ") (select "
					+ Merge.MEM_FIELDS + " from %s where OriCstGUID in(%s))";
			String sql = String.format(fmt, dropedTable, retainTable, idList);
			stmt.addBatch(sql);
			log.info(sql);

			fmt = "delete from %s where OriCstGUID in(%s)";
			// delete from
			sql = String.format(fmt, retainTable, idList);
			stmt.addBatch(sql);
			log.info(sql);

			fmt = "update %s set CstGUID=%s where OriCstGUID in(%s)";
			// update to
			sql = String.format(fmt, dropedTable, dropedGuid, idList);
			stmt.addBatch(sql);
			log.info(sql);
		}
	} //~restoreMember

	/** 恢复联名ID */
	protected void restoreCstAccount(Statement stmt, BackupCstInfo retain,
			BackupCstInfo droped) throws SQLException {
		long dropedGUID = droped.getCstGUID();
		String fmt = "update cst_Account set RealAccount=%s where CstGUID=%s";
		String sql = String.format(fmt, dropedGUID, dropedGUID);
		stmt.addBatch(sql);
		log.info(sql);
	} //~restoreCstAccount
} ///~Restore
